/*
Filename   : SqlHistoryCleanup.sql
Createdate : 2010-08-23
Author     : Niels Grove-Rasmussen
Description: Job history cleanup, also maintenance plan history.
             Based on functionality in maintenance plan task "History Cleanup Task"
             Runs on SQL Server 2005 SP1 and up.
             Disable SQLCMD as tokens are used in the job.
*/

USE [msdb]
GO

-- Get path for SQL Server Errorlog
DECLARE @errorlog TABLE (
	LogDate DATETIME
	,ProcessInfo NVARCHAR(MAX)
	,ErrorText NVARCHAR(MAX));
INSERT INTO @errorlog ([LogDate], [ProcessInfo], [ErrorText])
EXECUTE [master].[dbo].[sp_readerrorlog]
	@p1 = 0  -- 0 = current errorlog
	,@p2 = 1  -- 1 = SQL Server Errorlog
	,@p3 = N'Logging SQL Server messages in file ';
DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE([ErrorText],N'Logging SQL Server messages in file ''',''),N'\ERRORLOG''.','')
FROM @errorlog;
DELETE @errorlog;
-- Create name of logfile
DECLARE @_logfile NVARCHAR(max);
SET @_logfile = @errorlog_directory + N'\SqlHistoryCleanup.$(ESCAPE_SQUOTE(STRTDT))T$(ESCAPE_SQUOTE(STRTTM)).log';


-- Create job, step and schedule
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SqlHistoryCleanup', 
		@enabled=1, 
		@notify_level_eventlog=2, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'Job history cleanup, also maintenance plan history.
Based on functionality in maintenance plan task "History Cleanup Task".
Documentation and source is at CodePlex (http://sqlhistorycleanup.codeplex.com/).',
		@category_name=N'Database Maintenance', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute SqlHistoryCleanup', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=1, 
		@retry_interval=3, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE @_oldest_time DATETIME;
SET @_oldest_time = DATEADD(m, -3, GETDATE());  -- 3 months
DECLARE @_oldest_time_str NVARCHAR(128);
SET @_oldest_time_str = CONVERT(NVARCHAR(128), @_oldest_time, 126);
RAISERROR(N''Delete history older than %s...'', 0, 42, @_oldest_time_str) WITH NOWAIT;

EXECUTE [msdb].[dbo].[sp_delete_backuphistory]
	@oldest_date = @_oldest_time;

EXECUTE [msdb].[dbo].[sp_purge_jobhistory]
	@oldest_date = @_oldest_time;

EXECUTE [msdb].[dbo].[sp_maintplan_delete_log]
	@plan_id = NULL
	,@subplan_id = NULL
	,@oldest_time = @_oldest_time;', 
		@database_name=N'master', 
		@output_file_name= @_logfile, 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Schedule SqlHistoryCleanup', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=1, 
		@freq_subday_interval=0, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20100101, 
		@active_end_date=99991231, 
		@active_start_time=193700, 
		@active_end_time=235959;
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO
